package pkg_group5;

import java.sql.*;
import java.util.ArrayList;

public class MailingListDatabase {

    static final String driver = "com.mysql.jdbc.Driver";
    static final String url = "jdbc:mysql://127.0.0.1:3306/";
    private Connection conn;

    MailingListDatabase() {
        try {
            Class.forName(driver);
            conn = setupConnection();
        } catch (ClassNotFoundException cnf) {
            System.out.println(cnf);
        }
    }

    private static Connection setupConnection() {
        Connection sConn = null;

        try {
            sConn = DriverManager.getConnection(url + "mailinglist", "root", "");
        } catch (SQLException sqle) {
            System.out.println(sqle);
        }

        return sConn;
    }

    public int subscribe(String firstname, String surname, String emailAddress, int listID) {
        String query = "INSERT INTO userdetails (Firstname, Surname, EmailAddress, ListID) VALUES (?, ?, ?, ?)";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, firstname);
            ps.setString(2, surname);
            ps.setString(3, emailAddress);
            ps.setInt(4, listID);
            ps.executeUpdate();
            return 0;
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        return -1;
    }

    public int unsubscribe(String emailAddress) {

        String query = "DELETE FROM userdetails WHERE EmailAddress = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, emailAddress);
            ps.executeUpdate();
            return 0;
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        return 1;
    }

    public ArrayList getListOfSubscribers(int listID) {

        String query = "SELECT * FROM userdetails WHERE listID = ?";
        ArrayList subscriberArrayList = new ArrayList();
        MailingListUser user = new MailingListUser();

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, listID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                user = new MailingListUser();
                user.setFirstname(rs.getString(1));
                user.setSurname(rs.getString(2));
                user.setEmail(rs.getString(3));
                subscriberArrayList.add(user);
            }

        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        return subscriberArrayList;
    }

    public Boolean checkEmailIsNew(String emailAddress) {

        String query = "SELECT * FROM userdetails WHERE EmailAddress = ?";
        int rowCount = 0;

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, emailAddress);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                rowCount++;
            }
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        if (rowCount > 0) {
            return false;
        }
        else {
             return true;
        }
    }
     public Boolean checkListIDExists(int listID) {
        String query = "SELECT * FROM userdetails WHERE ListID = ?";
        int rowCount = 0;

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, listID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                rowCount++;
            }
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        if (rowCount > 0) {
            return true;
        }
        else {
             return false;
        }
    }
}
